# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql
# 加在配置文件信息
from scrapy.utils.project import get_project_settings

# useful for handling different item types with a single interface


class ScrapyReadBookPipeline:
    def __init__(self):
        self.file = None

    def open_spider(self, spider):
        self.file = open('book_list.json', 'w', encoding='utf-8')

    def close_spider(self, spider):
        self.file.close()

    def process_item(self, item, spider):
        self.file.write(str(item) + ',\n')
        return item





class Book2MysqlPipeline:
    def __init__(self):
        self.conn = None
        self.cursor = None
        self.charset = None
        self.password = None
        self.user = None
        self.db = None
        self.port = None
        self.host = None

    def open_spider(self, spider):
        settings = get_project_settings()
        self.host = settings['DB_HOST']
        self.port = settings['DB_PORT']
        self.db = settings['DB_NAME']
        self.user = settings['DB_USER']
        self.password = settings['DB_PASSWORD']
        self.charset = settings['DB_CHARSET']
        self.connect()

    def connect(self):
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            db=self.db,
            user=self.user,
            password=self.password,
            charset=self.charset
        )
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.conn.close()

    def process_item(self, item, spider):
        sql = f'Insert into spider_book ( book_name,book_author,book_img_url ) values ( \'{item["name"]}\',\'{item["author"]}\',\'{item["img"]}\' )'
        self.cursor.execute(sql)
        self.conn.commit()
        return item
